<?php
/**
 * Pipahr
 *
 * @author Randy Zhao
 * @copyright 2014 pipahr.com
 */

JTable::addIncludePath(JPATH_ADMINISTRATOR.'/components/com_puser/tables');
JTable::addIncludePath(JPATH_ADMINISTRATOR.'/components/com_jobs/tables');

/**
 * Registration controller class for Users.
 */
class PJobModelJob extends JModelLegacy
{
   public function getJobList($email=null, $mobiles=array(), $user_id="0", $type="applied", $start=0, $limit=10, $sort="time") {
      if (empty($email) && empty($mobiles))
         return false;

      $db = PFactory::getDbo();
      $where = " j.id IS NOT NULL AND hr.company_id <> 0 AND u.block=0 AND j.system=0 AND e.comp_name NOT LIKE \"PPPtest%\" AND u.email NOT LIKE \"%pipapaitest.com\" ";
      if (!empty($email) && empty($mobiles)) {
         $where .= " AND hr.email = ".$db->quote($email);
      } elseif (empty($email) && !empty($mobiles)) {
         $where .= " AND hr.phone IN (".implode(",", $mobiles).")";
      } else {
         $where .= " AND hr.email = ".$db->quote($email);
         // $where = " AND ( hr.email = ".$db->quote($email)." OR hr.phone IN (".implode(",", $mobiles).")";
      }

      switch ($type) {
         case 'received':
            $where .= " AND tk.token IS NOT NULL ";
            break;
         case 'favorite':
            // New feature
            break;
         case 'applied':
         default:
            // Do nothing
            break;
      }

      switch ($sort) {
         case 'time':
            $order = " ORDER by hr.date_added DESC ";
            break;
         case 'progress':
            $order = " ORDER by hr.online_interview_completion DESC ";
            break;
         default:
            $order = " ORDER by hr.date_added DESC ";
            break;
      }

      $query = "SELECT j.id AS job_id,j.job_title,j.employer_id AS company_id,j.contact_name,j.phone AS comp_phone,j.email AS comp_email,j.source,j.pos_txt,j.id_degree_level,j.id_pos_type,j.id_salary_type,j.id_job_exp,j.id_job_spec_categ,j.id_job_spec,j.id_pos_type2,j.id_fl1,j.id_fl_level1,j.id_fl2,j.id_fl_level2,j.agefrom,j.ageto,j.state,j.city,j.num_needed,j.long_desc,j.online_interview_requirements,j.system,
               e.comp_name AS company_title, tk.token, tk.auto_send, js.specialization, jje.exp_name AS job_exp, jst.salary_type AS salary,
               hr.id AS jobseeker_id,hr.name,hr.email,hr.sex,hr.dateofbirth,hr.phone,hr.match_degree,hr.online_interview_progress,hr.online_interview_completion,hr.is_new,hr.is_reviewed,
               ppr.end_time as personcreated,ppr.id as testedid,ppr.sheetid AS sheetid1,ppr.date_diff,
               ps.title as persontest_title,pj.sheetid AS persontest_id,jjp.position,jjp2.position AS position2,j.employment_type,
               jts.totaltime AS onlinetest_time,
               jdl.degree_level, jf1.name AS fl1, jf2.name AS fl2, jfl1.name AS fl_level1, jfl2.name AS fl_level2
               FROM *PREFIX*hr_jobseeker as hr
               LEFT JOIN #__jobs_job as j ON j.id=hr.job_id
               LEFT JOIN #__jobs_employer as e ON e.user_id=j.employer_id
               LEFT JOIN #__users as u ON u.id=e.user_id
               LEFT JOIN *PREFIX*hr_jobseeker_token as tk ON tk.jobseeker_id=hr.id AND tk.disable=0
               LEFT JOIN #__jobtest_jobsheet as jtjs ON jtjs.jobid=hr.job_id
               LEFT JOIN #__jobtest_sheet as jts ON jts.id=jtjs.sheetid
               LEFT JOIN #__persontest_jobsheet as pj ON pj.jobid=hr.job_id
               LEFT JOIN #__persontest_sheet as ps ON ps.id=pj.sheetid
               LEFT JOIN (SELECT id,end_time,sheetid,DATEDIFF(UTC_TIMESTAMP(),end_time) AS date_diff FROM #__persontest_result WHERE user_id=".intval($user_id)." GROUP BY sheetid ORDER BY end_time DESC) as ppr ON ppr.sheetid=pj.sheetid
               LEFT JOIN #__jobs_job_spec as js ON j.id_job_spec_categ=js.id
               LEFT JOIN #__jobs_job_exp as jje ON jje.id=j.id_job_exp
               LEFT JOIN #__jobs_salary_type as jst ON jst.id=j.id_salary_type
               LEFT JOIN #__jobs_job_position as jjp ON jjp.id=j.id_pos_type
               LEFT JOIN #__jobs_job_position as jjp2 ON jjp2.id=j.id_pos_type2
               LEFT JOIN #__jobs_degree_level as jdl ON jdl.id=j.id_degree_level
               LEFT JOIN #__jobs_fl as jf1 ON jf1.id=j.id_fl1
               LEFT JOIN #__jobs_fl_level as jfl1 ON jfl1.id=j.id_fl_level1
               LEFT JOIN #__jobs_fl as jf2 ON jf2.id=j.id_fl2
               LEFT JOIN #__jobs_fl_level as jfl2 ON jfl2.id=j.id_fl_level2
               WHERE ".$where."
               GROUP BY hr.id
               ".$order;

      // Try to load from cache
      $cache = CacheHelper::getCache('app_mobile');
      // get cached data now
      $joblist = $cache->call(array($this, '_getJobList'), $query, $start, $limit);
      // Event Driven Cache/Object Caching
      foreach ($joblist as $k => $jobpos) {
         if (!empty($jobpos['company_id'])) {
            CacheHelper::setTriggers(intval($jobpos['company_id']), $cache, array('*PREFIX*hr_jobseeker','*PREFIX*hr_jobseeker_token','#__jobs_job','#__jobs_employer','#__users','#__jobtest_jobsheet','#__persontest_jobsheet'));
         }
         if (!empty($jobpos['jobseeker_id'])) {
            CacheHelper::setTriggers(intval($jobpos['jobseeker_id']), $cache, array('*PREFIX*hr_jobseeker-'.$jobpos['jobseeker_id'],'*PREFIX*hr_jobseeker_token-'.$jobpos['jobseeker_id'],'#__persontest_result-'.$jobpos['jobseeker_id']));
         }
      }

      // Set trigger for jobseeker with userid (if we got one here)
      // When we update something in these table via persontest we can't get jobseekerid
      // By doing this, trigger remove will works fine
      CacheHelper::setTriggers($user_id, $cache, array('*PREFIX*hr_jobseeker','*PREFIX*hr_jobseeker_token','#__persontest_result'));

      return $joblist;
   }

   public function _getJobList($query, $start, $limit) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query), $start, $limit);
      return $db->loadAssocList();
   }

   public function getJobListCount($email=null, $mobiles=array(), $type="applied") {
      if (empty($email) && empty($mobiles)) {
         return false;
      }

      $db = PFactory::getDbo();
      $where = " j.id IS NOT NULL AND hr.company_id <> 0 AND u.block=0 AND j.system=0 AND e.comp_name NOT LIKE \"PPPtest%\" AND u.email NOT LIKE \"%pipapaitest.com\" ";
      if (!empty($email) && empty($mobiles)) {
         $where .= " AND hr.email = ".$db->quote($email);
      } elseif (empty($email) && !empty($mobiles)) {
         $where .= " AND hr.phone IN (".implode(",", $mobiles).")";
      } else {
         $where .= " AND hr.email = ".$db->quote($email);
         // $where = " AND ( hr.email = ".$db->quote($email)." OR hr.phone IN (".implode(",", $mobiles).")";
      }

      switch ($type) {
         case 'received':
            $where .= " AND tk.token IS NOT NULL ";
            break;
         case 'favorite':
            // New feature
            break;
         case 'applied':
         default:
            // Do nothing
            break;
      }

      $query = "SELECT count(*)
               FROM oc_hr_jobseeker AS hr
               LEFT JOIN #__jobs_job AS j ON j.id=hr.job_id
               LEFT JOIN #__jobs_employer AS e ON e.user_id=j.employer_id
               LEFT JOIN #__users AS u ON u.id=e.user_id
               LEFT JOIN *PREFIX*hr_jobseeker_token as tk ON tk.jobseeker_id=hr.id AND tk.disable=0
               WHERE ".$where;
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadResult() ? $db->loadResult() : 0;
   }

   public function getJobDetailByToken($token) {
      if (empty($token)) {
         return null;
      }

      $user = PFactory::getUser();
      $select = $join = "";
      if (!empty($user->id)) {
         $select = " ppr.end_time as personcreated,ppr.id as testedid,ppr.sheetid AS sheetid1,ppr.date_diff, ";
         $join = " LEFT JOIN (SELECT id,end_time,sheetid,DATEDIFF(UTC_TIMESTAMP(),end_time) AS date_diff FROM #__persontest_result WHERE user_id=".intval($user->id)." GROUP BY sheetid ORDER BY end_time DESC) as ppr ON ppr.sheetid=pj.sheetid ";
      }

      $db = PFactory::getDbo();
      $query = "SELECT j.id AS job_id,j.job_title,j.employer_id AS company_id,j.contact_name,j.phone AS comp_phone,j.email AS comp_email,j.source,j.pos_txt,j.id_degree_level,j.id_pos_type,j.id_salary_type,j.id_job_exp,j.id_job_spec_categ,j.id_job_spec,j.id_pos_type2,j.id_fl1,j.id_fl_level1,j.id_fl2,j.id_fl_level2,j.agefrom,j.ageto,j.state,j.city,j.num_needed,j.long_desc,j.online_interview_requirements,j.system,
         e.comp_name AS company_title, tk.token, tk.auto_send, js.specialization, jje.exp_name AS job_exp, jst.salary_type AS salary,
         hr.id AS jobseeker_id,hr.name,hr.email,hr.sex,hr.dateofbirth,hr.phone,hr.match_degree,hr.online_interview_progress,hr.online_interview_completion,hr.is_new,hr.is_reviewed,
         ps.title as persontest_title,pj.sheetid AS persontest_id,jjp.position,
         jts.totaltime AS onlinetest_time,
         ".$select."
         jdl.degree_level, jf1.name AS fl1, jf2.name AS fl2, jfl1.name AS fl_level1, jfl2.name AS fl_level2
         FROM *PREFIX*hr_jobseeker as hr
         LEFT JOIN #__jobs_job as j ON j.id=hr.job_id
         LEFT JOIN #__jobs_employer as e ON e.user_id=j.employer_id
         LEFT JOIN #__users as u ON u.id=e.user_id
         LEFT JOIN *PREFIX*hr_jobseeker_token as tk ON tk.jobseeker_id=hr.id AND tk.disable=0
         LEFT JOIN #__jobtest_jobsheet as jtjs ON jtjs.jobid=hr.job_id
         LEFT JOIN #__jobtest_sheet as jts ON jts.id=jtjs.sheetid
         LEFT JOIN #__persontest_jobsheet as pj ON pj.jobid=hr.job_id
         LEFT JOIN #__persontest_sheet as ps ON ps.id=pj.sheetid
         ".$join."
         LEFT JOIN #__jobs_job_spec as js ON j.id_job_spec_categ=js.id
         LEFT JOIN #__jobs_job_exp as jje ON jje.id=j.id_job_exp
         LEFT JOIN #__jobs_salary_type as jst ON jst.id=j.id_salary_type
         LEFT JOIN #__jobs_job_position as jjp ON jjp.id=j.id_pos_type
         LEFT JOIN #__jobs_degree_level as jdl ON jdl.id=j.id_degree_level
         LEFT JOIN #__jobs_fl as jf1 ON jf1.id=j.id_fl1
         LEFT JOIN #__jobs_fl_level as jfl1 ON jfl1.id=j.id_fl_level1
         LEFT JOIN #__jobs_fl as jf2 ON jf2.id=j.id_fl2
         LEFT JOIN #__jobs_fl_level as jfl2 ON jfl2.id=j.id_fl_level2
         WHERE  j.id IS NOT NULL AND hr.company_id <> 0 AND u.block=0 AND j.system=0 AND e.comp_name NOT LIKE \"PPPtest%\" AND u.email NOT LIKE \"%pipapaitest.com\" AND tk.token = ".$db->quote($token);

      // Try to load from cache
      $cache = CacheHelper::getCache('app_mobile');
      // get cached data now
      $jobdetail = $cache->call(array($this, '_getJobDetailByToken'), $query);

      // Event Driven Cache/Object Caching
      if (!empty($jobdetail['company_id'])) {
         CacheHelper::setTriggers(intval($jobdetail['company_id']), $cache, array('*PREFIX*hr_jobseeker','#__jobs_job','#__jobs_employer','#__users','*PREFIX*hr_jobseeker_token','#__jobtest_jobsheet','#__persontest_jobsheet'));
      }
      if (!empty($jobdetail['jobseeker_id'])) {
         CacheHelper::setTriggers(intval($jobdetail['jobseeker_id']), $cache, array('*PREFIX*hr_jobseeker-'.$jobdetail['jobseeker_id'],'*PREFIX*hr_jobseeker_token-'.$jobdetail['jobseeker_id'],'#__persontest_result-'.$jobdetail['jobseeker_id']));
      }

      // Set trigger for jobseeker with userid (if we got one here)
      // When we update something in these table via persontest we can't get jobseekerid
      // By doing this, trigger remove will works fine
      CacheHelper::setTriggers($user->id, $cache, array('*PREFIX*hr_jobseeker','*PREFIX*hr_jobseeker_token','#__persontest_result'));

      return $jobdetail;
   }

   public function _getJobDetailByToken($query) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadAssoc();
   }

}
